package com.teamsun.rptsql.mysql;

import com.teamsun.rptcfg.BaseRpt;
import com.teamsun.rptcfg.BaseRptColumn;
import com.teamsun.rptsql.AbstraceConcateSQL;
import com.teamsun.rptsql.IDBFunction;
import com.teamsun.util.Constants;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * <p>MYSQL数据库拼接SQL</p>
 */
public class MysqlConcateSQL extends AbstraceConcateSQL {

    private IDBFunction mysqlFunction;

    public MysqlConcateSQL(IDBFunction dbFunction) {
        this.mysqlFunction = dbFunction;
    }

    /**
     * ORACLE拼接分页查询SQL(CUBE数据)
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param startIndex 分页查询起始行
     * @param endIndex 分页查询结束行
     * @return
     */
    @Override
    public String concatePageSQL(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex) {
        String baseSelectSQL = concateSelectSQL(baseRpt, params, mysqlFunction);
        int offset = startIndex - 1;
        int rows = endIndex - offset;
        String pageSelectSQL = "SELECT c.* FROM (" + baseSelectSQL + ") c LIMIT " + offset + "," + rows;
        return pageSelectSQL;
    }

    /**
     * ORACLE拼接分页查询SQL(非CUBE数据)
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param startIndex 分页查询起始行
     * @param endIndex 分页查询结束行
     * @return
     */
    @Override
    public String concateCubePageSQL(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex) {
        String baseSelectSQL = concateCubeSelectSQL(baseRpt, params, mysqlFunction);
        int offset = startIndex - 1;
        int rows = endIndex - offset;
        String pageSelectSQL = "SELECT c.* FROM (" + baseSelectSQL + ") c LIMIT " + offset + "," + rows;
        return pageSelectSQL;
    }

    /**
     * 拼接汇总查询SQL
     * @param baseRpt 报表配置信息
     * @return
     */
    /*@Override
    public String concateSumSQL(BaseRpt baseRpt, String querySQL) {
        String sumSQL = "SELECT ";

        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();

        List<BaseRptColumn> allColumns = new ArrayList<BaseRptColumn>();
        allColumns.addAll(rptColumns);
        allColumns.addAll(hiddenRptColumns);

        int colCount = 0;
        for(BaseRptColumn rptColumn : rptColumns) {
            String aliasField = rptColumn.getAliasField();
            String field = rptColumn.getField();
            field = field.toLowerCase();

            // 该列是否为汇总列(0-否  1-是)
            String isSumCol = rptColumn.getIsSumCol();
            // 该列是否为计数列(0-否  1-是)
            String isCount = rptColumn.getIsCount();

            if(Constants.YES.equals(isSumCol)) {
                if(field.contains("sum(")) {
                    for(BaseRptColumn column : allColumns) {
                        String alias = column.getAliasField();
                        alias = alias.toLowerCase();
                        String compareAlias = "sum(" + alias + ")";

                        if(field.contains(compareAlias)) {
                            String replaceAlias = "sum(" + alias + ")";
                            field = field.replace(compareAlias, replaceAlias);
                        } else {
                            field = "SUM(" + aliasField + ")";
                        }
                    }
                    sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                } else {
                    sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
                }
            } else if(Constants.YES.equals(isCount)) {
                sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
            } else {
                if(colCount == 0) {
                    sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                } else {
                    sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                }
            }
            colCount ++;
        }

        for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
            String aliasField = hiddenRptColumn.getAliasField();
            String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
            if (!Constants.NO.equals(isQueryColumn)) {
                sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
            }
        }

        if(sumSQL.endsWith(",")) {
            sumSQL = sumSQL.substring(0, sumSQL.length() - 1);
        }

        sumSQL = sumSQL + " FROM (" + querySQL + ") t";
        return sumSQL;
    }*/

    /**
     * 拼接汇总SQL(sum和group by需要动态拼接)
     * @param baseRpt
     * @param params
     * @return
     */
    /*@Override
    public String concateCubeSumSQL(BaseRpt baseRpt, Map<String, String> params, String querySQL) {
        String selectDim = params.get(Constants.RPTDIM_KEY);  // 选择的维度
        String sumSQL = "SELECT ";

        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();

        List<BaseRptColumn> allColumns = new ArrayList<BaseRptColumn>();
        allColumns.addAll(rptColumns);
        allColumns.addAll(hiddenRptColumns);

        if(StringUtils.isBlank(selectDim)) {  // 未选择维度
            int colCount = 0;
            for(BaseRptColumn rptColumn : rptColumns) {
                String aliasField = rptColumn.getAliasField();
                String field = rptColumn.getField();
                field = field.toLowerCase();

                // 该列是否为汇总列(0-否  1-是)
                String isSumCol = rptColumn.getIsSumCol();
                // 该列是否为计数列(0-否  1-是)
                String isCount = rptColumn.getIsCount();

                if(Constants.YES.equals(isSumCol)) {
                    if(field.contains("sum(")) {
                        for(BaseRptColumn column : allColumns) {
                            String alias = column.getAliasField();
                            alias = alias.toLowerCase();
                            String compareAlias = "sum(" + alias + ")";

                            if(field.contains(compareAlias)) {
                                String replaceAlias = "sum(" + alias + ")";
                                field = field.replace(compareAlias, replaceAlias);
                            } else {
                                field = "SUM(" + aliasField + ")";
                            }
                        }
                        sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                    } else {
                        sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
                    }
                } else if(Constants.YES.equals(isCount)) {
                    sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
                } else {
                    if(colCount == 0) {
                        sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                    } else {
                        sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                    }
                }
                colCount ++;
            }
        } else {  // 选择维度
            int colCount = 0;
            String[] selectDims = selectDim.split(",");
            List<String> dimList = Arrays.asList(selectDims);
            for(BaseRptColumn rptColumn : rptColumns) {
                String aliasField = rptColumn.getAliasField();
                String field = rptColumn.getField();
                field = field.toLowerCase();
                if(Constants.YES.equals(rptColumn.getIsSumCol())) {
                    if(field.contains("sum(")) {
                        for(BaseRptColumn column : allColumns) {
                            String alias = column.getAliasField();
                            alias = alias.toLowerCase();
                            String compareAlias = "sum(" + alias + ")";

                            if(field.contains(compareAlias)) {
                                String replaceAlias = "sum(" + alias + ")";
                                field = field.replace(compareAlias, replaceAlias);
                            }
                        }
                        sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                    } else {
                        sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
                    }
                } else if(Constants.YES.equals(rptColumn.getIsCount())) {
                    sumSQL = sumSQL + "SUM(" + aliasField + ") AS \"" + aliasField + "\",";
                } else {
                    if(dimList.contains(aliasField)) {
                        if(colCount == 0) {
                            sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                        } else {
                            sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                        }
                        colCount ++;
                    }
                }
            }
        }

        for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
            String aliasField = hiddenRptColumn.getAliasField();
            String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
            if (!Constants.NO.equals(isQueryColumn)) {
                sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
            }
        }

        if(sumSQL.endsWith(",")) {
            sumSQL = sumSQL.substring(0, sumSQL.length() - 1);
        }

        sumSQL = sumSQL + " FROM (" + querySQL + ") t";
        return sumSQL;
    }*/
}
